/*
	This Procedure loads all the Running, Hold and Shipped Lots Inventory Cost in Chiptrac.

 	EXEC sp_inventory_summary_cost_report_proc '2399','0','0','0','0','0','7/8/2007:19:57:39'
select * from lot_table where lot_current_status = 'running'
	lot_dropout = 0
	Need to activate the commented condition for Lot Category when lot category in lot table is not null
*/

alter  PROC sp_inventory_summary_cost_report_proc

	@PRODUCT_ID 	VARCHAR(200),
	@VENDOR		VARCHAR(50),
	@ASOFDATE	VARCHAR(50),
	@STATUS		VARCHAR(50),
	@LOT_CATEGORY	VARCHAR(50),
	@REPORT_TYPE	VARCHAR(20),
	@DEFAULT_PROMPT	VARCHAR(50)

AS
BEGIN

CREATE TABLE #INVENTORY_SUMMARY_TABLE ( FAB_LOT_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),WAFER_QTY_IN NUMERIC(18,5),QTY_IN NUMERIC(18,5),
					PART_NUMBER VARCHAR(50), PROCESSSTEP_ID INT,BASE_PRODUCT VARCHAR(50),PS_TYPE VARCHAR(50))

DECLARE @RESULTS TABLE ( BASE_PRODUCT VARCHAR(50),PART_NUMBER VARCHAR(50),
			FAB_QTY NUMERIC(18,5),FAB_OPTION NUMERIC(18,5),WAFER_STORE_QTY NUMERIC(18,5),DIE_BANK_QTY NUMERIC(18,5),
			ASSEMBLY_QTY NUMERIC(18,5),TEST_FPO NUMERIC(18,5),TEST_FT NUMERIC(18,5), CLASS_STORE_QTY NUMERIC(18,5),
			FINISHED_GOODS_QTY NUMERIC(18,5) )

DECLARE @SQLSTRING 		NVARCHAR(4000)

DECLARE @BASE_PRODUCT		VARCHAR(50)
DECLARE @PART_NUMBER 		VARCHAR(50)
DECLARE @PS_TYPE 		VARCHAR(50)
DECLARE @PACKAGE_TYPE 		VARCHAR(50)
DECLARE @WAFER_QTY_IN 		NUMERIC(18,5)
DECLARE @QTY_IN 		NUMERIC(18,5)

DECLARE @FAB_QTY 		NUMERIC(18,5)
DECLARE @FAB_OPTION 		NUMERIC(18,5)
DECLARE @WAFER_STORE_QTY 	NUMERIC(18,5)
DECLARE @DIE_BANK_QTY 		NUMERIC(18,5)
DECLARE @ASSEMBLY_QTY 		NUMERIC(18,5)
DECLARE @TEST_FPO		NUMERIC(18,5)
DECLARE @TEST_FT		NUMERIC(18,5)
DECLARE @CLASS_STORE_QTY 	NUMERIC(18,5)
DECLARE @FINISHED_GOODS_QTY 	NUMERIC(18,5)

DECLARE @TEMP_VARCHAR 	VARCHAR(50)
DECLARE @START_DATE 	DATETIME
DECLARE @END_DATE 	DATETIME

DECLARE @LOT_CURSOR 		CURSOR


	SET @SQLSTRING = N'INSERT INTO #INVENTORY_SUMMARY_TABLE
		SELECT  FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, ( ISNULL(LOT_CUMMULATIVE_COST,0) ), ( ISNULL(LOT_CUMMULATIVE_COST,0) ),--LOT_CATEGORY,L_VENDOR_NAME, 
		PART_NUMBER,P.PROCESSSTEP_ID,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			CASE 
				WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
			END 
		) AS BASE_PRODUCT,PS_TYPE '
	
	IF @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE_HISTORY LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '

		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	END
		SET @SQLSTRING = @SQLSTRING + ' WHERE 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		AND P_TYPE IS NULL
		AND P.OBSOLETE != 0 '

	IF @ASOFDATE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE LTH.LOT_ID = LT.LOT_ID AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
	END

	IF @PRODUCT_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P.P_PARENT_PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ISNULL(LT.LOT_CATEGORY, ''PROD'') =  ''' +  @LOT_CATEGORY + '''' 
		--SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CATEGORY =  ''' +  @LOT_CATEGORY + '''' 
	END 
	IF @VENDOR <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L.VENDOR_ID =  ''' +  @VENDOR + '''' 
	END
	IF @STATUS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CURRENT_STATUS =  ''' +  @STATUS + '''' 
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )' 
	END	
		
	--PRINT @SQLSTRING
		
	EXEC(@SQLSTRING)
	
	INSERT INTO @RESULTS ( BASE_PRODUCT,PART_NUMBER ) (SELECT DISTINCT BASE_PRODUCT,PART_NUMBER FROM #INVENTORY_SUMMARY_TABLE )

	SET @LOT_CURSOR = CURSOR FOR 

			SELECT 
				SUM(WAFER_QTY_IN),SUM(QTY_IN),PART_NUMBER,BASE_PRODUCT,PS_TYPE
			from 
				#INVENTORY_SUMMARY_TABLE
			GROUP BY 
				BASE_PRODUCT,PART_NUMBER,PS_TYPE

	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @WAFER_QTY_IN,@QTY_IN,@PART_NUMBER,@BASE_PRODUCT,@PS_TYPE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @PS_TYPE = 'FAB')
		BEGIN
			SET @FAB_QTY = @WAFER_QTY_IN
			--SET @DIE_TOTAL_FOR_WAFERS = ( @WAFER_QTY_IN * ISNULL(@GROSS_DIE,0) )
								
			UPDATE @RESULTS SET FAB_QTY = @FAB_QTY--,FAB_DIE_QTY = @DIE_TOTAL_FOR_WAFERS
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Fab Option')
		BEGIN
			SET @FAB_OPTION = @WAFER_QTY_IN
			--SET @DIE_TOTAL_FOR_WAFERS = ( @WAFER_QTY_IN * ISNULL(@GROSS_DIE,0) )
								
			UPDATE @RESULTS SET FAB_OPTION = @FAB_OPTION--,FAB_OPTION_DIE_QTY = @DIE_TOTAL_FOR_WAFERS
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Wafer Bank')
		BEGIN
			SET @WAFER_STORE_QTY = @WAFER_QTY_IN
			--SET @DIE_TOTAL_FOR_WAFERS = ( @WAFER_QTY_IN * ISNULL(@GROSS_DIE,0) )
						
			UPDATE @RESULTS SET WAFER_STORE_QTY = @WAFER_STORE_QTY--,WS_DIE_QTY = @DIE_TOTAL_FOR_WAFERS
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Die Bank')
		BEGIN
			SET @DIE_BANK_QTY = @QTY_IN
			
			UPDATE @RESULTS SET DIE_BANK_QTY = @DIE_BANK_QTY
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Assembly')
		BEGIN
			SET @ASSEMBLY_QTY = @QTY_IN

			UPDATE @RESULTS SET ASSEMBLY_QTY = @ASSEMBLY_QTY
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
			
		END
		ELSE IF ( @PS_TYPE = 'FPO')
		BEGIN
			SET @TEST_FPO = @QTY_IN

			UPDATE @RESULTS SET TEST_FPO = @TEST_FPO
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
			
		END
		ELSE IF ( @PS_TYPE = 'FT')
		BEGIN
			SET @TEST_FT = @QTY_IN

			UPDATE @RESULTS SET TEST_FT = @TEST_FT
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
			
		END
		ELSE IF ( @PS_TYPE = 'Tape & Reel')
		BEGIN
			SET @CLASS_STORE_QTY= @QTY_IN
			
			UPDATE @RESULTS SET CLASS_STORE_QTY = @CLASS_STORE_QTY
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'FG')
		BEGIN
			SET @FINISHED_GOODS_QTY = @QTY_IN
			
			UPDATE @RESULTS SET FINISHED_GOODS_QTY = @FINISHED_GOODS_QTY
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
			
			SET @FAB_QTY  		= NULL
			SET @FAB_OPTION  	= NULL
			SET @WAFER_STORE_QTY  	= NULL
			SET @DIE_BANK_QTY  	= NULL
			SET @ASSEMBLY_QTY  	= NULL
			SET @TEST_FPO 		= NULL
			SET @TEST_FT 		= NULL
			SET @CLASS_STORE_QTY  	= NULL
			SET @FINISHED_GOODS_QTY = NULL

			SET @BASE_PRODUCT	= NULL
			SET @PART_NUMBER 	= NULL
			SET @WAFER_QTY_IN 	= NULL
			SET @QTY_IN 		= NULL
			SET @PS_TYPE 		= NULL

		FETCH NEXT FROM @LOT_CURSOR INTO @WAFER_QTY_IN,@QTY_IN,@PART_NUMBER,@BASE_PRODUCT,@PS_TYPE
	END
 	CLOSE @LOT_CURSOR
 	DEALLOCATE @LOT_CURSOR

	SELECT 
		BASE_PRODUCT,PART_NUMBER,FAB_QTY,FAB_OPTION,WAFER_STORE_QTY,
		DIE_BANK_QTY,ASSEMBLY_QTY,TEST_FPO,TEST_FT,CLASS_STORE_QTY,FINISHED_GOODS_QTY
	FROM @RESULTS 
	ORDER BY BASE_PRODUCT,PART_NUMBER
END
